package com.rickpan.repository;

import com.rickpan.entity.PasswordResetCode;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 密码重置验证码Repository
 */
@Repository
public interface PasswordResetCodeRepository extends JpaRepository<PasswordResetCode, Long> {

    /**
     * 根据邮箱和验证码查找有效的验证码记录
     */
    @Query("SELECT p FROM PasswordResetCode p WHERE p.email = :email AND p.code = :code AND p.used = false AND p.expiresAt > :now ORDER BY p.createdAt DESC")
    Optional<PasswordResetCode> findValidCodeByEmailAndCode(@Param("email") String email, 
                                                           @Param("code") String code, 
                                                           @Param("now") LocalDateTime now);

    /**
     * 根据邮箱查找所有未使用的验证码
     */
    @Query("SELECT p FROM PasswordResetCode p WHERE p.email = :email AND p.used = false ORDER BY p.createdAt DESC")
    List<PasswordResetCode> findUnusedCodesByEmail(@Param("email") String email);

    /**
     * 根据邮箱查找最新的验证码
     */
    @Query("SELECT p FROM PasswordResetCode p WHERE p.email = :email ORDER BY p.createdAt DESC")
    List<PasswordResetCode> findByEmailOrderByCreatedAtDesc(@Param("email") String email);

    /**
     * 标记邮箱的所有未使用验证码为已使用
     */
    @Modifying
    @Query("UPDATE PasswordResetCode p SET p.used = true, p.usedAt = :usedAt WHERE p.email = :email AND p.used = false")
    void markAllUnusedCodesAsUsed(@Param("email") String email, @Param("usedAt") LocalDateTime usedAt);

    /**
     * 删除过期的验证码
     */
    @Modifying
    @Query("DELETE FROM PasswordResetCode p WHERE p.expiresAt < :now")
    void deleteExpiredCodes(@Param("now") LocalDateTime now);

    /**
     * 统计邮箱在指定时间内发送的验证码数量
     */
    @Query("SELECT COUNT(p) FROM PasswordResetCode p WHERE p.email = :email AND p.createdAt > :since")
    long countByEmailAndCreatedAtAfter(@Param("email") String email, @Param("since") LocalDateTime since);
}
